import pymysql
from openpyxl import load_workbook

con = pymysql.connect(user="root",
                      passwd="root",
                      db="bangjiang",
                      host="127.0.0.1",
                      local_infile=1)

con.set_charset('utf8')
cur = con.cursor()



# 打开一个workbook
wb = load_workbook(filename="D:/帮江项目/卡口信息表-vehicle_barrier_city_export-20220902.xlsx")

# 获取当前活跃的worksheet,默认就是第一个worksheet
# ws = wb.active

# 当然也可以使用下面的方法

# 获取所有表格(worksheet)的名字
sheets = wb.get_sheet_names()
# 第一个表格的名称
sheet_first = sheets[0]
# 获取特定的worksheet
ws = wb.get_sheet_by_name(sheet_first)

# 获取表格所有行和列，两者都是可迭代的
rows = ws.rows
columns = ws.columns

# 迭代所有的行
datas = []
skip_count = 2
for row in rows:
    if skip_count != 0:
        skip_count -= 1
        continue
    skip_col_count = 1
    data = []
    for col in row:
        if skip_col_count != 0:
            skip_col_count -= 1
            continue
        data.append(col.value)
    if data[1] == '[停]社-东莞市中堂镇中堂车站停车场(入口)':
        print(data)
    datas.append(data)


# print('datas is {}'.format(datas))
# cur.executemany("insert into gate_info(gate_id, gate_name, gate_address, road_name, road_type, direction_type, direction_desc, gate_type, near, gate_longitude, gate_latitude, gate_state, trigger_type, road_count, start_time, end_time) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", datas)
# con.commit()